package com.jn.zhinongbao.adapter.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.supportingagriculturaltreasures.bean.Cart;
import com.example.supportingagriculturaltreasures.bean.Order;
import com.example.supportingagriculturaltreasures.bean.Product;
import com.example.supportingagriculturaltreasures.bean.Purchase;
import com.example.supportingagriculturaltreasures.bean.User;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

public class DatabaseHelper extends SQLiteOpenHelper {

    // 数据库信息
    private static final String DATABASE_NAME = "agricultural_treasures.db";
    private static final int DATABASE_VERSION = 7;

    // 用户表
    private static final String TABLE_USERS = "users";
    private static final String COLUMN_ID = "id";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_PHONE = "phone";
    private static final String COLUMN_ROLE = "role";
    private static final String COLUMN_SHOP_NAME = "shop_name";

    // 货品表
    private static final String TABLE_PRODUCTS = "products";
    private static final String COLUMN_PRODUCT_ID = "id";
    private static final String COLUMN_PRODUCT_NAME = "name";
    private static final String COLUMN_PRODUCT_PRICE = "price";
    private static final String COLUMN_PRODUCT_LOCATION = "location";
    private static final String COLUMN_PRODUCT_CONTACT_PHONE = "contact_phone";
    private static final String COLUMN_PRODUCT_IMAGE_URL = "image_url";
    private static final String COLUMN_PRODUCT_CATEGORY = "category";
    private static final String COLUMN_PRODUCT_SHOP_NAME = "shop_name";
    private static final String COLUMN_PRODUCT_MERCHANT_NAME = "merchant_name";
    private static final String COLUMN_PRODUCT_MERCHANT_ID = "merchant_id";
    private static final String COLUMN_PRODUCT_LIKES = "likes";
    private static final String COLUMN_PRODUCT_CREATE_TIME = "create_time";

    // 采购表
    private static final String TABLE_PURCHASES = "purchases";
    private static final String COLUMN_PURCHASE_ID = "id";
    private static final String COLUMN_PURCHASE_IMAGE_URL = "image_url";
    private static final String COLUMN_PURCHASE_ADDRESS = "address";
    private static final String COLUMN_PURCHASE_REQUIREMENTS = "requirements";
    private static final String COLUMN_PURCHASE_PRICE = "purchase_price";
    private static final String COLUMN_PURCHASE_SHOP_NAME = "shop_name";
    private static final String COLUMN_PURCHASE_MERCHANT_NAME = "merchant_name";
    private static final String COLUMN_PURCHASE_MERCHANT_ID = "merchant_id";
    private static final String COLUMN_PURCHASE_CREATE_TIME = "create_time";

    // 订单表
    private static final String TABLE_ORDERS = "orders";
    private static final String COLUMN_ORDER_ID = "id";
    private static final String COLUMN_ORDER_USER_ID = "user_id";
    private static final String COLUMN_ORDER_MERCHANT_ID = "merchant_id";
    private static final String COLUMN_ORDER_PRODUCT_ID = "product_id";
    private static final String COLUMN_ORDER_PRODUCT_NAME = "product_name";
    private static final String COLUMN_ORDER_PRODUCT_PRICE = "product_price";
    private static final String COLUMN_ORDER_PRODUCT_IMAGE_URL = "product_image_url";
    private static final String COLUMN_ORDER_QUANTITY = "quantity";
    private static final String COLUMN_ORDER_TOTAL_AMOUNT = "total_amount";
    private static final String COLUMN_ORDER_STATUS = "status";
    private static final String COLUMN_ORDER_CREATE_TIME = "create_time";
    private static final String COLUMN_ORDER_UPDATE_TIME = "update_time";
    private static final String COLUMN_ORDER_SHIPPING_ADDRESS = "shipping_address";
    private static final String COLUMN_ORDER_CONTACT_PHONE = "contact_phone";

    // 购物车表
    private static final String TABLE_CART = "cart";
    private static final String COLUMN_CART_ID = "id";
    private static final String COLUMN_CART_USER_ID = "user_id";
    private static final String COLUMN_CART_MERCHANT_ID = "merchant_id";
    private static final String COLUMN_CART_PRODUCT_ID = "product_id";
    private static final String COLUMN_CART_PRODUCT_NAME = "product_name";
    private static final String COLUMN_CART_PRODUCT_PRICE = "product_price";
    private static final String COLUMN_CART_PRODUCT_IMAGE_URL = "product_image_url";
    private static final String COLUMN_CART_QUANTITY = "quantity";
    private static final String COLUMN_CART_CREATE_TIME = "create_time";
    private static final String COLUMN_CART_UPDATE_TIME = "update_time";

    // 新闻表
    private static final String TABLE_NEWS = "news";
    private static final String COLUMN_NEWS_ID = "id";
    private static final String COLUMN_NEWS_TITLE = "title";
    private static final String COLUMN_NEWS_SUMMARY = "summary";
    private static final String COLUMN_NEWS_CONTENT = "content";
    private static final String COLUMN_NEWS_IMAGE_URL = "image_url";
    private static final String COLUMN_NEWS_AUTHOR = "author";
    private static final String COLUMN_NEWS_AUTHOR_ID = "author_id";
    private static final String COLUMN_NEWS_CATEGORY = "category";
    private static final String COLUMN_NEWS_VIEW_COUNT = "view_count";
    private static final String COLUMN_NEWS_CREATE_TIME = "create_time";
    private static final String COLUMN_NEWS_UPDATE_TIME = "update_time";

    // 农友圈表
    private static final String TABLE_FARMER_CIRCLE = "farmer_circle";
    private static final String COLUMN_FARMER_CIRCLE_ID = "id";
    private static final String COLUMN_FARMER_CIRCLE_CONTENT = "content";
    private static final String COLUMN_FARMER_CIRCLE_IMAGE_URL = "image_url";
    private static final String COLUMN_FARMER_CIRCLE_AUTHOR_NAME = "author_name";
    private static final String COLUMN_FARMER_CIRCLE_AUTHOR_ID = "author_id";
    private static final String COLUMN_FARMER_CIRCLE_LOCATION = "location";
    private static final String COLUMN_FARMER_CIRCLE_LIKE_COUNT = "like_count";
    private static final String COLUMN_FARMER_CIRCLE_COMMENT_COUNT = "comment_count";
    private static final String COLUMN_FARMER_CIRCLE_CREATE_TIME = "create_time";
    private static final String COLUMN_FARMER_CIRCLE_UPDATE_TIME = "update_time";

    // 聊天室表
    private static final String TABLE_CHAT_ROOMS = "chat_rooms";
    private static final String COLUMN_CHAT_ROOM_ID = "id";
    private static final String COLUMN_CHAT_ROOM_USER_ID = "user_id";
    private static final String COLUMN_CHAT_ROOM_MERCHANT_ID = "merchant_id";
    private static final String COLUMN_CHAT_ROOM_USER_NAME = "user_name";
    private static final String COLUMN_CHAT_ROOM_MERCHANT_NAME = "merchant_name";
    private static final String COLUMN_CHAT_ROOM_LAST_MESSAGE = "last_message";
    private static final String COLUMN_CHAT_ROOM_LAST_MESSAGE_TIME = "last_message_time";
    private static final String COLUMN_CHAT_ROOM_CREATE_TIME = "create_time";
    private static final String COLUMN_CHAT_ROOM_UPDATE_TIME = "update_time";

    // 聊天消息表
    private static final String TABLE_CHAT_MESSAGES = "chat_messages";
    private static final String COLUMN_CHAT_MESSAGE_ID = "id";
    private static final String COLUMN_CHAT_MESSAGE_ROOM_ID = "room_id";
    private static final String COLUMN_CHAT_MESSAGE_SENDER_ID = "sender_id";
    private static final String COLUMN_CHAT_MESSAGE_SENDER_NAME = "sender_name";
    private static final String COLUMN_CHAT_MESSAGE_CONTENT = "content";
    private static final String COLUMN_CHAT_MESSAGE_TYPE = "message_type";
    private static final String COLUMN_CHAT_MESSAGE_SEND_TIME = "send_time";
    private static final String COLUMN_CHAT_MESSAGE_IS_READ = "is_read";

    // 创建用户表的SQL语句
    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_USERS + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_USERNAME + " TEXT UNIQUE NOT NULL,"
            + COLUMN_PASSWORD + " TEXT NOT NULL,"
            + COLUMN_PHONE + " TEXT NOT NULL,"
            + COLUMN_ROLE + " INTEGER NOT NULL,"
            + COLUMN_SHOP_NAME + " TEXT"
            + ")";

    // 创建货品表的SQL语句
    private static final String CREATE_TABLE_PRODUCTS = "CREATE TABLE " + TABLE_PRODUCTS + "("
            + COLUMN_PRODUCT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_PRODUCT_NAME + " TEXT NOT NULL,"
            + COLUMN_PRODUCT_PRICE + " REAL NOT NULL,"
            + COLUMN_PRODUCT_LOCATION + " TEXT NOT NULL,"
            + COLUMN_PRODUCT_CONTACT_PHONE + " TEXT NOT NULL,"
            + COLUMN_PRODUCT_IMAGE_URL + " TEXT,"
            + COLUMN_PRODUCT_CATEGORY + " TEXT NOT NULL,"
            + COLUMN_PRODUCT_SHOP_NAME + " TEXT,"
            + COLUMN_PRODUCT_MERCHANT_NAME + " TEXT,"
            + COLUMN_PRODUCT_MERCHANT_ID + " INTEGER NOT NULL,"
            + COLUMN_PRODUCT_LIKES + " INTEGER DEFAULT 0,"
            + COLUMN_PRODUCT_CREATE_TIME + " TEXT"
            + ")";

    // 创建采购表的SQL语句
    private static final String CREATE_TABLE_PURCHASES = "CREATE TABLE " + TABLE_PURCHASES + "("
            + COLUMN_PURCHASE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_PURCHASE_IMAGE_URL + " TEXT,"
            + COLUMN_PURCHASE_ADDRESS + " TEXT NOT NULL,"
            + COLUMN_PURCHASE_REQUIREMENTS + " TEXT NOT NULL,"
            + COLUMN_PURCHASE_PRICE + " REAL NOT NULL,"
            + COLUMN_PURCHASE_SHOP_NAME + " TEXT,"
            + COLUMN_PURCHASE_MERCHANT_NAME + " TEXT,"
            + COLUMN_PURCHASE_MERCHANT_ID + " INTEGER NOT NULL,"
            + COLUMN_PURCHASE_CREATE_TIME + " TEXT"
            + ")";

    // 创建订单表的SQL语句
    private static final String CREATE_TABLE_ORDERS = "CREATE TABLE " + TABLE_ORDERS + "("
            + COLUMN_ORDER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_ORDER_USER_ID + " INTEGER NOT NULL,"
            + COLUMN_ORDER_MERCHANT_ID + " INTEGER NOT NULL,"
            + COLUMN_ORDER_PRODUCT_ID + " INTEGER NOT NULL,"
            + COLUMN_ORDER_PRODUCT_NAME + " TEXT NOT NULL,"
            + COLUMN_ORDER_PRODUCT_PRICE + " REAL NOT NULL,"
            + COLUMN_ORDER_PRODUCT_IMAGE_URL + " TEXT,"
            + COLUMN_ORDER_QUANTITY + " INTEGER NOT NULL,"
            + COLUMN_ORDER_TOTAL_AMOUNT + " REAL NOT NULL,"
            + COLUMN_ORDER_STATUS + " INTEGER NOT NULL,"
            + COLUMN_ORDER_CREATE_TIME + " TEXT NOT NULL,"
            + COLUMN_ORDER_UPDATE_TIME + " TEXT NOT NULL,"
            + COLUMN_ORDER_SHIPPING_ADDRESS + " TEXT NOT NULL,"
            + COLUMN_ORDER_CONTACT_PHONE + " TEXT NOT NULL"
            + ")";

    // 创建购物车表的SQL语句
    private static final String CREATE_TABLE_CART = "CREATE TABLE " + TABLE_CART + "("
            + COLUMN_CART_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_CART_USER_ID + " INTEGER NOT NULL,"
            + COLUMN_CART_MERCHANT_ID + " INTEGER NOT NULL,"
            + COLUMN_CART_PRODUCT_ID + " INTEGER NOT NULL,"
            + COLUMN_CART_PRODUCT_NAME + " TEXT NOT NULL,"
            + COLUMN_CART_PRODUCT_PRICE + " REAL NOT NULL,"
            + COLUMN_CART_PRODUCT_IMAGE_URL + " TEXT,"
            + COLUMN_CART_QUANTITY + " INTEGER NOT NULL,"
            + COLUMN_CART_CREATE_TIME + " TEXT NOT NULL,"
            + COLUMN_CART_UPDATE_TIME + " TEXT NOT NULL"
            + ")";

    // 创建新闻表的SQL语句
    private static final String CREATE_TABLE_NEWS = "CREATE TABLE " + TABLE_NEWS + "("
            + COLUMN_NEWS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_NEWS_TITLE + " TEXT NOT NULL,"
            + COLUMN_NEWS_SUMMARY + " TEXT NOT NULL,"
            + COLUMN_NEWS_CONTENT + " TEXT NOT NULL,"
            + COLUMN_NEWS_IMAGE_URL + " TEXT,"
            + COLUMN_NEWS_AUTHOR + " TEXT NOT NULL,"
            + COLUMN_NEWS_AUTHOR_ID + " INTEGER NOT NULL,"
            + COLUMN_NEWS_CATEGORY + " TEXT NOT NULL,"
            + COLUMN_NEWS_VIEW_COUNT + " INTEGER DEFAULT 0,"
            + COLUMN_NEWS_CREATE_TIME + " TEXT NOT NULL,"
            + COLUMN_NEWS_UPDATE_TIME + " TEXT NOT NULL"
            + ")";

    // 创建农友圈表的SQL语句
    private static final String CREATE_TABLE_FARMER_CIRCLE = "CREATE TABLE " + TABLE_FARMER_CIRCLE + "("
            + COLUMN_FARMER_CIRCLE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_FARMER_CIRCLE_CONTENT + " TEXT NOT NULL,"
            + COLUMN_FARMER_CIRCLE_IMAGE_URL + " TEXT,"
            + COLUMN_FARMER_CIRCLE_AUTHOR_NAME + " TEXT NOT NULL,"
            + COLUMN_FARMER_CIRCLE_AUTHOR_ID + " INTEGER NOT NULL,"
            + COLUMN_FARMER_CIRCLE_LOCATION + " TEXT NOT NULL,"
            + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " INTEGER DEFAULT 0,"
            + COLUMN_FARMER_CIRCLE_COMMENT_COUNT + " INTEGER DEFAULT 0,"
            + COLUMN_FARMER_CIRCLE_CREATE_TIME + " TEXT NOT NULL,"
            + COLUMN_FARMER_CIRCLE_UPDATE_TIME + " TEXT NOT NULL"
            + ")";

    // 创建聊天室表的SQL语句
    private static final String CREATE_TABLE_CHAT_ROOMS = "CREATE TABLE " + TABLE_CHAT_ROOMS + "("
            + COLUMN_CHAT_ROOM_ID + " TEXT PRIMARY KEY,"
            + COLUMN_CHAT_ROOM_USER_ID + " INTEGER NOT NULL,"
            + COLUMN_CHAT_ROOM_MERCHANT_ID + " INTEGER NOT NULL,"
            + COLUMN_CHAT_ROOM_USER_NAME + " TEXT NOT NULL,"
            + COLUMN_CHAT_ROOM_MERCHANT_NAME + " TEXT NOT NULL,"
            + COLUMN_CHAT_ROOM_LAST_MESSAGE + " TEXT,"
            + COLUMN_CHAT_ROOM_LAST_MESSAGE_TIME + " TEXT NOT NULL,"
            + COLUMN_CHAT_ROOM_CREATE_TIME + " TEXT NOT NULL,"
            + COLUMN_CHAT_ROOM_UPDATE_TIME + " TEXT NOT NULL"
            + ")";

    // 创建聊天消息表的SQL语句
    private static final String CREATE_TABLE_CHAT_MESSAGES = "CREATE TABLE " + TABLE_CHAT_MESSAGES + "("
            + COLUMN_CHAT_MESSAGE_ID + " TEXT PRIMARY KEY,"
            + COLUMN_CHAT_MESSAGE_ROOM_ID + " TEXT NOT NULL,"
            + COLUMN_CHAT_MESSAGE_SENDER_ID + " INTEGER NOT NULL,"
            + COLUMN_CHAT_MESSAGE_SENDER_NAME + " TEXT NOT NULL,"
            + COLUMN_CHAT_MESSAGE_CONTENT + " TEXT NOT NULL,"
            + COLUMN_CHAT_MESSAGE_TYPE + " INTEGER DEFAULT 0,"
            + COLUMN_CHAT_MESSAGE_SEND_TIME + " TEXT NOT NULL,"
            + COLUMN_CHAT_MESSAGE_IS_READ + " INTEGER DEFAULT 0"
            + ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * 获取当前日期字符串 (年-月-日)
     */
    private String getCurrentDateString() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
        return sdf.format(new Date());
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_PRODUCTS);
        db.execSQL(CREATE_TABLE_PURCHASES);
        db.execSQL(CREATE_TABLE_ORDERS);
        db.execSQL(CREATE_TABLE_CART);
        db.execSQL(CREATE_TABLE_NEWS);
        db.execSQL(CREATE_TABLE_FARMER_CIRCLE);
        db.execSQL(CREATE_TABLE_CHAT_ROOMS);
        db.execSQL(CREATE_TABLE_CHAT_MESSAGES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 只添加新表，不删除现有数据
        if (oldVersion < 6) {
            db.execSQL(CREATE_TABLE_FARMER_CIRCLE);
        }
        if (oldVersion < 7) {
            db.execSQL(CREATE_TABLE_CHAT_ROOMS);
            db.execSQL(CREATE_TABLE_CHAT_MESSAGES);
        }
    }

    /**
     * 用户注册
     */
    public boolean registerUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USERNAME, user.getUsername());
        values.put(COLUMN_PASSWORD, user.getPassword());
        values.put(COLUMN_PHONE, user.getPhone());
        values.put(COLUMN_ROLE, user.getRole());
        if (user.getShopName() != null) {
            values.put(COLUMN_SHOP_NAME, user.getShopName());
        }

        long result = db.insert(TABLE_USERS, null, values);
        db.close();
        return result != -1;
    }

    /**
     * 用户登录
     */
    public User loginUser(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS,
                new String[]{COLUMN_ID, COLUMN_USERNAME, COLUMN_PASSWORD, COLUMN_PHONE, COLUMN_ROLE, COLUMN_SHOP_NAME},
                COLUMN_USERNAME + "=? AND " + COLUMN_PASSWORD + "=?",
                new String[]{username, password},
                null, null, null);

        User user = null;
        if (cursor.moveToFirst()) {
            user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)));
            user.setUsername(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USERNAME)));
            user.setPassword(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PASSWORD)));
            user.setPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PHONE)));
            user.setRole(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ROLE)));
            String shopName = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_SHOP_NAME));
            if (shopName != null) {
                user.setShopName(shopName);
            }
        }
        cursor.close();
        db.close();
        return user;
    }

    /**
     * 检查用户名是否已存在
     */
    public boolean isUsernameExists(String username) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS,
                new String[]{COLUMN_ID},
                COLUMN_USERNAME + "=?",
                new String[]{username},
                null, null, null);

        boolean exists = cursor.getCount() > 0;
        cursor.close();
        db.close();
        return exists;
    }

    /**
     * 根据ID获取用户信息
     */
    public User getUserById(int userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        User user = null;
        
        try {
            cursor = db.query(TABLE_USERS,
                new String[]{COLUMN_ID, COLUMN_USERNAME, COLUMN_PASSWORD, COLUMN_PHONE, COLUMN_ROLE, COLUMN_SHOP_NAME},
                COLUMN_ID + "=?",
                new String[]{String.valueOf(userId)},
                null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
            user = new User();
            user.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID)));
            user.setUsername(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_USERNAME)));
            user.setPassword(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PASSWORD)));
            user.setPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PHONE)));
            user.setRole(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ROLE)));
                
                // 处理shop_name，可能为null
                int shopNameIndex = cursor.getColumnIndex(COLUMN_SHOP_NAME);
                if (shopNameIndex != -1 && !cursor.isNull(shopNameIndex)) {
                    user.setShopName(cursor.getString(shopNameIndex));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        
        return user;
    }

    /**
     * 添加货品
     */
    public boolean addProduct(Product product) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PRODUCT_NAME, product.getName());
        values.put(COLUMN_PRODUCT_PRICE, product.getPrice());
        values.put(COLUMN_PRODUCT_LOCATION, product.getLocation());
        values.put(COLUMN_PRODUCT_CONTACT_PHONE, product.getContactPhone());
        values.put(COLUMN_PRODUCT_IMAGE_URL, product.getImageUrl());
        values.put(COLUMN_PRODUCT_CATEGORY, product.getCategory());
        values.put(COLUMN_PRODUCT_SHOP_NAME, product.getShopName());
        values.put(COLUMN_PRODUCT_MERCHANT_NAME, product.getMerchantName());
        values.put(COLUMN_PRODUCT_MERCHANT_ID, product.getMerchantId());
        values.put(COLUMN_PRODUCT_LIKES, product.getLikes());
        values.put(COLUMN_PRODUCT_CREATE_TIME, System.currentTimeMillis());

        long result = db.insert(TABLE_PRODUCTS, null, values);
        db.close();
        return result != -1;
    }

    /**
     * 获取所有货品
     */
    public List<Product> getAllProducts() {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PRODUCTS, null, null, null, null, null, 
                COLUMN_PRODUCT_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_ID)));
                product.setName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_PRICE)));
                product.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LOCATION)));
                product.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CONTACT_PHONE)));
                product.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_IMAGE_URL)));
                product.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CATEGORY)));
                product.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_SHOP_NAME)));
                product.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_NAME)));
                product.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_ID)));
                product.setLikes(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LIKES)));
                products.add(product);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return products;
    }

    /**
     * 根据商家ID获取货品
     */
    public List<Product> getProductsByMerchantId(int merchantId) {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PRODUCTS, null, 
                COLUMN_PRODUCT_MERCHANT_ID + "=?", new String[]{String.valueOf(merchantId)}, 
                null, null, COLUMN_PRODUCT_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_ID)));
                product.setName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_PRICE)));
                product.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LOCATION)));
                product.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CONTACT_PHONE)));
                product.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_IMAGE_URL)));
                product.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CATEGORY)));
                product.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_SHOP_NAME)));
                product.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_NAME)));
                product.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_ID)));
                product.setLikes(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LIKES)));
                products.add(product);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return products;
    }

    /**
     * 添加采购
     */
    public boolean addPurchase(Purchase purchase) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PURCHASE_IMAGE_URL, purchase.getImageUrl());
        values.put(COLUMN_PURCHASE_ADDRESS, purchase.getAddress());
        values.put(COLUMN_PURCHASE_REQUIREMENTS, purchase.getRequirements());
        values.put(COLUMN_PURCHASE_PRICE, purchase.getPurchasePrice());
        values.put(COLUMN_PURCHASE_SHOP_NAME, purchase.getShopName());
        values.put(COLUMN_PURCHASE_MERCHANT_NAME, purchase.getMerchantName());
        values.put(COLUMN_PURCHASE_MERCHANT_ID, purchase.getMerchantId());
        values.put(COLUMN_PURCHASE_CREATE_TIME, System.currentTimeMillis());

        long result = db.insert(TABLE_PURCHASES, null, values);
        db.close();
        return result != -1;
    }

    /**
     * 获取所有采购
     */
    public List<Purchase> getAllPurchases() {
        List<Purchase> purchases = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PURCHASES, null, null, null, null, null, 
                COLUMN_PURCHASE_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Purchase purchase = new Purchase();
                purchase.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_ID)));
                purchase.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_IMAGE_URL)));
                purchase.setAddress(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_ADDRESS)));
                purchase.setRequirements(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_REQUIREMENTS)));
                purchase.setPurchasePrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_PRICE)));
                purchase.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_SHOP_NAME)));
                purchase.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_MERCHANT_NAME)));
                purchase.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_MERCHANT_ID)));
                purchase.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_CREATE_TIME)));
                purchases.add(purchase);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return purchases;
    }

    /**
     * 根据商家ID获取采购
     */
    public List<Purchase> getPurchasesByMerchantId(int merchantId) {
        List<Purchase> purchases = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_PURCHASES, null, 
                COLUMN_PURCHASE_MERCHANT_ID + "=?", new String[]{String.valueOf(merchantId)}, 
                null, null, COLUMN_PURCHASE_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Purchase purchase = new Purchase();
                purchase.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_ID)));
                purchase.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_IMAGE_URL)));
                purchase.setAddress(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_ADDRESS)));
                purchase.setRequirements(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_REQUIREMENTS)));
                purchase.setPurchasePrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_PRICE)));
                purchase.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_SHOP_NAME)));
                purchase.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_MERCHANT_NAME)));
                purchase.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_MERCHANT_ID)));
                purchase.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PURCHASE_CREATE_TIME)));
                purchases.add(purchase);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return purchases;
    }

    /**
     * 添加订单
     */
    public boolean addOrder(Order order) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ORDER_USER_ID, order.getUserId());
        values.put(COLUMN_ORDER_MERCHANT_ID, order.getMerchantId());
        values.put(COLUMN_ORDER_PRODUCT_ID, order.getProductId());
        values.put(COLUMN_ORDER_PRODUCT_NAME, order.getProductName());
        values.put(COLUMN_ORDER_PRODUCT_PRICE, order.getProductPrice());
        values.put(COLUMN_ORDER_PRODUCT_IMAGE_URL, order.getProductImageUrl());
        values.put(COLUMN_ORDER_QUANTITY, order.getQuantity());
        values.put(COLUMN_ORDER_TOTAL_AMOUNT, order.getTotalAmount());
        values.put(COLUMN_ORDER_STATUS, order.getStatus());
        values.put(COLUMN_ORDER_CREATE_TIME, getCurrentDateString());
        values.put(COLUMN_ORDER_UPDATE_TIME, getCurrentDateString());
        values.put(COLUMN_ORDER_SHIPPING_ADDRESS, order.getShippingAddress());
        values.put(COLUMN_ORDER_CONTACT_PHONE, order.getContactPhone());

        long result = db.insert(TABLE_ORDERS, null, values);
        db.close();
        return result != -1;
    }

    /**
     * 根据用户ID获取订单
     */
    public List<Order> getOrdersByUserId(int userId) {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_ORDERS, null, 
                COLUMN_ORDER_USER_ID + "=?", new String[]{String.valueOf(userId)}, 
                null, null, COLUMN_ORDER_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Order order = new Order();
                order.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_ID)));
                order.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_USER_ID)));
                order.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_MERCHANT_ID)));
                order.setProductId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_ID)));
                order.setProductName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_NAME)));
                order.setProductPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_PRICE)));
                order.setProductImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_IMAGE_URL)));
                order.setQuantity(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_QUANTITY)));
                order.setTotalAmount(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_TOTAL_AMOUNT)));
                order.setStatus(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_STATUS)));
                order.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CREATE_TIME)));
                order.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_UPDATE_TIME)));
                order.setShippingAddress(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_SHIPPING_ADDRESS)));
                order.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CONTACT_PHONE)));
                orders.add(order);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return orders;
    }

    /**
     * 根据商家ID获取订单
     */
    public List<Order> getOrdersByMerchantId(int merchantId) {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_ORDERS, null, 
                COLUMN_ORDER_MERCHANT_ID + "=?", new String[]{String.valueOf(merchantId)}, 
                null, null, COLUMN_ORDER_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Order order = new Order();
                order.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_ID)));
                order.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_USER_ID)));
                order.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_MERCHANT_ID)));
                order.setProductId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_ID)));
                order.setProductName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_NAME)));
                order.setProductPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_PRICE)));
                order.setProductImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_IMAGE_URL)));
                order.setQuantity(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_QUANTITY)));
                order.setTotalAmount(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_TOTAL_AMOUNT)));
                order.setStatus(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_STATUS)));
                order.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CREATE_TIME)));
                order.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_UPDATE_TIME)));
                order.setShippingAddress(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_SHIPPING_ADDRESS)));
                order.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CONTACT_PHONE)));
                orders.add(order);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return orders;
    }

    /**
     * 更新订单状态
     */
    public boolean updateOrderStatus(int orderId, int status) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ORDER_STATUS, status);
        
        // 统一使用当前日期字符串格式
        values.put(COLUMN_ORDER_UPDATE_TIME, getCurrentDateString());

        int result = db.update(TABLE_ORDERS, values, COLUMN_ORDER_ID + "=?", 
                new String[]{String.valueOf(orderId)});
        db.close();
        return result > 0;
    }

    /**
     * 添加到购物车
     */
    public boolean addToCart(Cart cart) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        // 先检查购物车中是否已存在相同商品
        Cursor cursor = db.query(TABLE_CART, null, 
                COLUMN_CART_USER_ID + "=? AND " + COLUMN_CART_PRODUCT_ID + "=?",
                new String[]{String.valueOf(cart.getUserId()), String.valueOf(cart.getProductId())},
                null, null, null);
        
        boolean result = false;
        if (cursor.moveToFirst()) {
            // 如果已存在，更新数量
            int existingQuantity = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_QUANTITY));
            int newQuantity = existingQuantity + cart.getQuantity();
            
            ContentValues values = new ContentValues();
            values.put(COLUMN_CART_QUANTITY, newQuantity);
            values.put(COLUMN_CART_UPDATE_TIME, getCurrentDateString());
            
            int updateResult = db.update(TABLE_CART, values, 
                    COLUMN_CART_USER_ID + "=? AND " + COLUMN_CART_PRODUCT_ID + "=?",
                    new String[]{String.valueOf(cart.getUserId()), String.valueOf(cart.getProductId())});
            result = updateResult > 0;
        } else {
            // 如果不存在，插入新记录
            ContentValues values = new ContentValues();
            values.put(COLUMN_CART_USER_ID, cart.getUserId());
            values.put(COLUMN_CART_MERCHANT_ID, cart.getMerchantId());
            values.put(COLUMN_CART_PRODUCT_ID, cart.getProductId());
            values.put(COLUMN_CART_PRODUCT_NAME, cart.getProductName());
            values.put(COLUMN_CART_PRODUCT_PRICE, cart.getProductPrice());
            values.put(COLUMN_CART_PRODUCT_IMAGE_URL, cart.getProductImageUrl());
            values.put(COLUMN_CART_QUANTITY, cart.getQuantity());
            values.put(COLUMN_CART_CREATE_TIME, getCurrentDateString());
            values.put(COLUMN_CART_UPDATE_TIME, getCurrentDateString());

            long insertResult = db.insert(TABLE_CART, null, values);
            result = insertResult != -1;
        }
        
        cursor.close();
        db.close();
        return result;
    }

    /**
     * 根据用户ID获取购物车商品
     */
    public List<Cart> getCartByUserId(int userId) {
        List<Cart> cartItems = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_CART, null, 
                COLUMN_CART_USER_ID + "=?", new String[]{String.valueOf(userId)}, 
                null, null, COLUMN_CART_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Cart cart = new Cart();
                cart.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_ID)));
                cart.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_USER_ID)));
                cart.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_MERCHANT_ID)));
                cart.setProductId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_PRODUCT_ID)));
                cart.setProductName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CART_PRODUCT_NAME)));
                cart.setProductPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_CART_PRODUCT_PRICE)));
                cart.setProductImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CART_PRODUCT_IMAGE_URL)));
                cart.setQuantity(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CART_QUANTITY)));
                cart.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CART_CREATE_TIME)));
                cart.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CART_UPDATE_TIME)));
                cartItems.add(cart);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return cartItems;
    }

    /**
     * 更新购物车商品数量
     */
    public boolean updateCartQuantity(int cartId, int quantity) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_CART_QUANTITY, quantity);
        values.put(COLUMN_CART_UPDATE_TIME, getCurrentDateString());

        int result = db.update(TABLE_CART, values, COLUMN_CART_ID + "=?", 
                new String[]{String.valueOf(cartId)});
        db.close();
        return result > 0;
    }

    /**
     * 从购物车移除商品
     */
    public boolean removeFromCart(int cartId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_CART, COLUMN_CART_ID + "=?", 
                new String[]{String.valueOf(cartId)});
        db.close();
        return result > 0;
    }

    /**
     * 清空用户购物车
     */
    public boolean clearCart(int userId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_CART, COLUMN_CART_USER_ID + "=?", 
                new String[]{String.valueOf(userId)});
        db.close();
        return result > 0;
    }

    /**
     * 根据用户ID和状态获取订单
     */
    public List<Order> getOrdersByUserIdAndStatus(int userId, int status) {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_ORDERS, null, 
                COLUMN_ORDER_USER_ID + "=? AND " + COLUMN_ORDER_STATUS + "=?", 
                new String[]{String.valueOf(userId), String.valueOf(status)}, 
                null, null, COLUMN_ORDER_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Order order = new Order();
                order.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_ID)));
                order.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_USER_ID)));
                order.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_MERCHANT_ID)));
                order.setProductId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_ID)));
                order.setProductName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_NAME)));
                order.setProductPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_PRICE)));
                order.setProductImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_PRODUCT_IMAGE_URL)));
                order.setQuantity(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_QUANTITY)));
                order.setTotalAmount(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_TOTAL_AMOUNT)));
                order.setStatus(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ORDER_STATUS)));
                order.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CREATE_TIME)));
                order.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_UPDATE_TIME)));
                order.setShippingAddress(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_SHIPPING_ADDRESS)));
                order.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ORDER_CONTACT_PHONE)));
                orders.add(order);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return orders;
    }

    /**
     * 删除订单
     */
    public boolean deleteOrder(int orderId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_ORDERS, COLUMN_ORDER_ID + "=?", 
                new String[]{String.valueOf(orderId)});
        db.close();
        return result > 0;
    }

    /**
     * 创建测试商品数据（带图片URL）
     */
    public void createTestProductsWithImages() {
        // 获取测试商家
        User testMerchant = loginUser("testmerchant", "123456");
        if (testMerchant == null) return;
        
        // 先清除旧的测试数据
        clearTestProducts();
        
        // 示例图片URL（使用一些公开的图片URL）
        String[] imageUrls = {
            "https://images.unsplash.com/photo-1552318965-6e6be7484ada?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1542838132-92c53300491e?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1571771894821-ce9b6c11b08e?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1587049633312-d628ae50a8ae?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1560598520-9b4a7c6c9b5e?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1544465429-ce2b5ce3df05?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1544465429-ce2b5ce3df05?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1586201375761-83865001e31c?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1595855759920-86582396756a?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1586201375761-83865001e31c?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1595855759920-86582396756a?w=400&h=400&fit=crop",
            "https://images.unsplash.com/photo-1586201375761-83865001e31c?w=400&h=400&fit=crop"
        };

        // 水果蔬菜类商品
        String[] fruitVegetableNames = {"有机苹果", "新鲜白菜", "胡萝卜", "西红柿", "黄瓜"};
        double[] fruitVegetablePrices = {12.8, 8.5, 9.2, 15.0, 6.5};
        
        // 米面粮油类商品
        String[] grainOilNames = {"优质大米", "精制面粉", "花生油", "玉米"};
        double[] grainOilPrices = {25.0, 18.0, 45.0, 8.0};
        
        // 农资农具类商品
        String[] farmToolNames = {"有机肥料", "农用喷雾器", "园艺剪"};
        double[] farmToolPrices = {35.0, 120.0, 25.0};

        // 创建水果蔬菜类商品
        for (int i = 0; i < fruitVegetableNames.length; i++) {
            Product product = new Product();
            product.setName(fruitVegetableNames[i]);
            product.setPrice(fruitVegetablePrices[i]);
            product.setLocation("测试农场");
            product.setContactPhone("13800000000");
            product.setImageUrl(imageUrls[i]);
            product.setCategory("水果蔬菜");
            product.setShopName(testMerchant.getShopName());
            product.setMerchantName(testMerchant.getUsername());
            product.setMerchantId(testMerchant.getId());
            product.setLikes(0);

            addProduct(product);
        }
        
        // 创建米面粮油类商品
        for (int i = 0; i < grainOilNames.length; i++) {
            Product product = new Product();
            product.setName(grainOilNames[i]);
            product.setPrice(grainOilPrices[i]);
            product.setLocation("测试农场");
            product.setContactPhone("13800000000");
            product.setImageUrl(imageUrls[i + 5]);
            product.setCategory("米面粮油");
            product.setShopName(testMerchant.getShopName());
            product.setMerchantName(testMerchant.getUsername());
            product.setMerchantId(testMerchant.getId());
            product.setLikes(0);

            addProduct(product);
        }
        
        // 创建农资农具类商品
        for (int i = 0; i < farmToolNames.length; i++) {
            Product product = new Product();
            product.setName(farmToolNames[i]);
            product.setPrice(farmToolPrices[i]);
            product.setLocation("测试农场");
            product.setContactPhone("13800000000");
            product.setImageUrl(imageUrls[i + 9]);
            product.setCategory("农资农具");
            product.setShopName(testMerchant.getShopName());
            product.setMerchantName(testMerchant.getUsername());
            product.setMerchantId(testMerchant.getId());
            product.setLikes(0);

            addProduct(product);
        }
    }

    /**
     * 创建测试购物车和订单数据
     */
    public void createTestCartAndOrderData() {
        // 获取测试用户
        User testUser = loginUser("testuser", "123456");
        User testMerchant = loginUser("testmerchant", "123456");
        
        if (testUser == null || testMerchant == null) return;
        
        // 检查是否已经有测试数据
        List<Cart> existingCart = getCartByUserId(testUser.getId());
        List<Order> existingOrders = getOrdersByUserId(testUser.getId());
        if (!existingCart.isEmpty() && !existingOrders.isEmpty()) return;
        
        // 获取商品列表
        List<Product> products = getProductsByMerchantId(testMerchant.getId());
        
        if (!products.isEmpty()) {
            // 创建测试购物车数据
            if (existingCart.isEmpty()) {
                for (int i = 0; i < Math.min(3, products.size()); i++) {
                    Product product = products.get(i);
                    Cart cart = new Cart(
                        testUser.getId(),
                        product.getMerchantId(),
                        product.getId(),
                        product.getName(),
                        product.getPrice(),
                        product.getImageUrl(),
                        i + 1 // 数量
                    );
                    cart.setSelected(true); // 默认选中
                    addToCart(cart);
                }
            }

            // 创建测试订单数据
            if (existingOrders.isEmpty()) {
                for (int i = 0; i < Math.min(4, products.size()); i++) {
                    Product product = products.get(i);
                    Order order = new Order(
                        testUser.getId(),
                        product.getMerchantId(),
                        product.getId(),
                        product.getName(),
                        product.getPrice(),
                        product.getImageUrl(),
                        1,
                        "测试地址：北京市朝阳区xxx路xxx号",
                        "13900000000"
                    );
                    
                    // 设置不同状态
                    switch (i % 5) {
                        case 0:
                            order.setStatus(1); // 已付款
                            break;
                        case 1:
                            order.setStatus(2); // 待发货
                            break;
                        case 2:
                            order.setStatus(3); // 已发货
                            break;
                        case 3:
                            order.setStatus(4); // 确认收货
                            break;
                        case 4:
                            order.setStatus(5); // 已取消
                            break;
                    }
                    
                    addOrder(order);
                }
            }
        }
    }

    /**
     * 删除商品
     */
    public boolean deleteProduct(int productId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_PRODUCTS, COLUMN_PRODUCT_ID + "=?", 
                new String[]{String.valueOf(productId)});
        db.close();
        return result > 0;
    }

    /**
     * 更新商品信息
     */
    public boolean updateProduct(Product product) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PRODUCT_NAME, product.getName());
        values.put(COLUMN_PRODUCT_PRICE, product.getPrice());
        values.put(COLUMN_PRODUCT_LOCATION, product.getLocation());
        values.put(COLUMN_PRODUCT_CONTACT_PHONE, product.getContactPhone());
        values.put(COLUMN_PRODUCT_IMAGE_URL, product.getImageUrl());
        values.put(COLUMN_PRODUCT_CATEGORY, product.getCategory());

        int result = db.update(TABLE_PRODUCTS, values, COLUMN_PRODUCT_ID + "=?", 
                new String[]{String.valueOf(product.getId())});
        db.close();
        return result > 0;
    }

    /**
     * 根据ID获取商品信息
     */
    public Product getProductById(int productId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        Product product = null;
        
        try {
            cursor = db.query(TABLE_PRODUCTS, null, 
                COLUMN_PRODUCT_ID + "=?", new String[]{String.valueOf(productId)}, 
                null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_ID)));
                product.setName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_PRICE)));
                product.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LOCATION)));
                product.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CONTACT_PHONE)));
                product.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_IMAGE_URL)));
                product.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CATEGORY)));
                product.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_SHOP_NAME)));
                product.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_NAME)));
                product.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_ID)));
                product.setLikes(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LIKES)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        
        return product;
    }

    /**
     * 创建今日测试销售数据（用于测试）
     */
    public void createTodayTestSalesData() {
        User testMerchant = loginUser("testmerchant", "123456");
        User testUser = loginUser("testuser", "123456");
        
        if (testMerchant == null || testUser == null) {
            android.util.Log.d("DatabaseHelper", "Test users not found, cannot create test sales data");
            return;
        }

        // 获取商家的商品
        List<Product> products = getProductsByMerchantId(testMerchant.getId());
        if (products.isEmpty()) {
            android.util.Log.d("DatabaseHelper", "No products found for merchant, cannot create test sales data");
            return;
        }

        // 创建今日确认收货的订单
        for (int i = 0; i < Math.min(2, products.size()); i++) {
            Product product = products.get(i);
            Order order = new Order(
                testUser.getId(),
                testMerchant.getId(),
                product.getId(),
                product.getName(),
                product.getPrice(),
                product.getImageUrl(),
                1,
                "测试地址：今日销售测试",
                "13900000000"
            );
            
            // 设置订单为确认收货状态
            order.setStatus(4); // STATUS_CONFIRMED = 4
            
            if (addOrder(order)) {
                android.util.Log.d("DatabaseHelper", "Created test order for today sales test");
            }
        }
    }

    /**
     * 获取今日销售额 (按商家ID)
     */
    public double getTodaySales(int merchantId) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
        String today = sdf.format(new Date());
        return getSalesByDatePrefix(merchantId, today);
    }

    /**
     * 获取本月销售额 (按商家ID)
     */
    public double getMonthSales(int merchantId) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM", Locale.getDefault());
        String currentMonth = sdf.format(new Date());
        return getSalesByDatePrefix(merchantId, currentMonth);
    }

    /**
     * 获取总销售额 (按商家ID)
     */
    public double getTotalSales(int merchantId) {
        return getSalesByDateRange(merchantId, null, null);
    }

    /**
     * 根据日期范围获取销售额
     */
    private double getSalesByDateRange(int merchantId, String startDate, String endDate) {
        SQLiteDatabase db = this.getReadableDatabase();
        double totalSales = 0.0;
        Cursor cursor = null;
        
        try {
            String selection = COLUMN_ORDER_MERCHANT_ID + "=? AND " + COLUMN_ORDER_STATUS + "=?";
            String[] selectionArgs;
            
            if (startDate != null && endDate != null) {
                // 使用LIKE查询匹配日期前缀
                selection += " AND " + COLUMN_ORDER_UPDATE_TIME + " LIKE ?";
                selectionArgs = new String[]{String.valueOf(merchantId), "4", startDate + "%"}; // 4 = STATUS_CONFIRMED
            } else {
                selectionArgs = new String[]{String.valueOf(merchantId), "4"}; // 4 = STATUS_CONFIRMED
            }
            
            cursor = db.query(TABLE_ORDERS, new String[]{COLUMN_ORDER_TOTAL_AMOUNT}, 
                    selection, selectionArgs, null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    totalSales += cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_TOTAL_AMOUNT));
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        
        return totalSales;
    }

    /**
     * 根据日期前缀获取销售额 (用于月份统计)
     */
    private double getSalesByDatePrefix(int merchantId, String datePrefix) {
        SQLiteDatabase db = this.getReadableDatabase();
        double totalSales = 0.0;
        Cursor cursor = null;
        
        try {
            String selection = COLUMN_ORDER_MERCHANT_ID + "=? AND " + COLUMN_ORDER_STATUS + "=? AND " + 
                             COLUMN_ORDER_UPDATE_TIME + " LIKE ?";
            String[] selectionArgs = {String.valueOf(merchantId), "4", datePrefix + "%"}; // 4 = STATUS_CONFIRMED
            
            cursor = db.query(TABLE_ORDERS, new String[]{COLUMN_ORDER_TOTAL_AMOUNT}, 
                    selection, selectionArgs, null, null, null);
            
            if (cursor != null && cursor.moveToFirst()) {
                do {
                    totalSales += cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_ORDER_TOTAL_AMOUNT));
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        
        return totalSales;
    }

    // =========================== 新闻相关方法 ===========================

    /**
     * 添加新闻
     */
    public boolean addNews(com.example.supportingagriculturaltreasures.bean.News news) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_NEWS_TITLE, news.getTitle());
        values.put(COLUMN_NEWS_SUMMARY, news.getSummary());
        values.put(COLUMN_NEWS_CONTENT, news.getContent());
        values.put(COLUMN_NEWS_IMAGE_URL, news.getImageUrl());
        values.put(COLUMN_NEWS_AUTHOR, news.getAuthor());
        values.put(COLUMN_NEWS_AUTHOR_ID, news.getAuthorId());
        values.put(COLUMN_NEWS_CATEGORY, news.getCategory());
        values.put(COLUMN_NEWS_VIEW_COUNT, news.getViewCount());
        values.put(COLUMN_NEWS_CREATE_TIME, news.getCreateTime());
        values.put(COLUMN_NEWS_UPDATE_TIME, news.getUpdateTime());

        long result = db.insert(TABLE_NEWS, null, values);
        db.close();
        return result != -1;
    }

    /**
     * 获取所有新闻
     */
    public List<com.example.supportingagriculturaltreasures.bean.News> getAllNews() {
        List<com.example.supportingagriculturaltreasures.bean.News> newsList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NEWS, null, null, null, null, null, 
                COLUMN_NEWS_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                com.example.supportingagriculturaltreasures.bean.News news = new com.example.supportingagriculturaltreasures.bean.News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_TITLE)));
                news.setSummary(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_SUMMARY)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CONTENT)));
                news.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_IMAGE_URL)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR)));
                news.setAuthorId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR_ID)));
                news.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CATEGORY)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_UPDATE_TIME)));
                newsList.add(news);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return newsList;
    }

    /**
     * 根据分类获取新闻
     */
    public List<com.example.supportingagriculturaltreasures.bean.News> getNewsByCategory(String category) {
        List<com.example.supportingagriculturaltreasures.bean.News> newsList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NEWS, null, 
                COLUMN_NEWS_CATEGORY + "=?", new String[]{category}, 
                null, null, COLUMN_NEWS_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                com.example.supportingagriculturaltreasures.bean.News news = new com.example.supportingagriculturaltreasures.bean.News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_TITLE)));
                news.setSummary(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_SUMMARY)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CONTENT)));
                news.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_IMAGE_URL)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR)));
                news.setAuthorId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR_ID)));
                news.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CATEGORY)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_UPDATE_TIME)));
                newsList.add(news);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return newsList;
    }

    /**
     * 根据ID获取新闻
     */
    public com.example.supportingagriculturaltreasures.bean.News getNewsById(int newsId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = null;
        com.example.supportingagriculturaltreasures.bean.News news = null;
        
        try {
            cursor = db.query(TABLE_NEWS, null, 
                COLUMN_NEWS_ID + "=?", new String[]{String.valueOf(newsId)}, 
                null, null, null);

            if (cursor != null && cursor.moveToFirst()) {
                news = new com.example.supportingagriculturaltreasures.bean.News();
                news.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_ID)));
                news.setTitle(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_TITLE)));
                news.setSummary(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_SUMMARY)));
                news.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CONTENT)));
                news.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_IMAGE_URL)));
                news.setAuthor(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR)));
                news.setAuthorId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_AUTHOR_ID)));
                news.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CATEGORY)));
                news.setViewCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_VIEW_COUNT)));
                news.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_CREATE_TIME)));
                news.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NEWS_UPDATE_TIME)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            db.close();
        }
        
        return news;
    }

    /**
     * 增加新闻阅读量
     */
    public boolean incrementNewsViewCount(int newsId) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        // 先获取当前阅读量
        int currentViewCount = 0;
        Cursor cursor = db.query(TABLE_NEWS, new String[]{COLUMN_NEWS_VIEW_COUNT}, 
                COLUMN_NEWS_ID + "=?", new String[]{String.valueOf(newsId)}, 
                null, null, null);
        
        if (cursor.moveToFirst()) {
            currentViewCount = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_NEWS_VIEW_COUNT));
        }
        cursor.close();
        
        // 更新阅读量
        ContentValues values = new ContentValues();
        values.put(COLUMN_NEWS_VIEW_COUNT, currentViewCount + 1);
        
        int result = db.update(TABLE_NEWS, values, COLUMN_NEWS_ID + "=?", 
                new String[]{String.valueOf(newsId)});
        db.close();
        return result > 0;
    }

    /**
     * 删除新闻
     */
    public boolean deleteNews(int newsId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_NEWS, COLUMN_NEWS_ID + "=?", 
                new String[]{String.valueOf(newsId)});
        db.close();
        return result > 0;
    }

    /**
     * 更新新闻
     */
    public boolean updateNews(com.example.supportingagriculturaltreasures.bean.News news) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_NEWS_TITLE, news.getTitle());
        values.put(COLUMN_NEWS_SUMMARY, news.getSummary());
        values.put(COLUMN_NEWS_CONTENT, news.getContent());
        values.put(COLUMN_NEWS_IMAGE_URL, news.getImageUrl());
        values.put(COLUMN_NEWS_CATEGORY, news.getCategory());
        values.put(COLUMN_NEWS_UPDATE_TIME, news.getUpdateTime());

        int result = db.update(TABLE_NEWS, values, COLUMN_NEWS_ID + "=?", 
                new String[]{String.valueOf(news.getId())});
        db.close();
        return result > 0;
    }

    /**
     * 创建示例新闻数据
     */
    public void createSampleNewsData() {
        // 检查是否已有新闻数据
        List<com.example.supportingagriculturaltreasures.bean.News> existingNews = getAllNews();
        if (!existingNews.isEmpty()) {
            return; // 已有数据，不重复创建
        }

        String currentTime = getCurrentTimeString();
        
        // 创建示例新闻
        com.example.supportingagriculturaltreasures.bean.News[] sampleNews = {
            new com.example.supportingagriculturaltreasures.bean.News(
                "春季蔬菜种植技术要点",
                "春季是蔬菜种植的关键时期，掌握正确的种植技术对提高产量和品质至关重要。",
                "<html><body><h2>春季蔬菜种植技术要点</h2><p>春季是蔬菜种植的关键时期，需要注意以下几个方面：</p><h3>1. 土壤准备</h3><p>春季土壤解冻后，要及时进行翻耕，深度以20-25厘米为宜。施入充足的有机肥，每亩施用腐熟的农家肥3000-4000公斤。</p><h3>2. 种子处理</h3><p>播种前要对种子进行处理，包括晒种、温汤浸种、药剂拌种等，以提高种子的发芽率和抗病能力。</p><h3>3. 播种时间</h3><p>根据当地气候条件和蔬菜品种特性，选择适宜的播种时间。一般来说，喜温蔬菜在土温稳定在10℃以上时播种。</p></body></html>",
                null, "农技专家", 1, "种植技术"
            ),
            new com.example.supportingagriculturaltreasures.bean.News(
                "现代化养殖技术与管理",
                "随着科技的发展，现代化养殖技术为养殖业带来了新的机遇和挑战。",
                "<html><body><h2>现代化养殖技术与管理</h2><p>现代化养殖技术的发展为畜牧业带来了革命性的变化：</p><h3>1. 智能化设备</h3><p>采用自动化饲喂系统、环境控制系统等，提高养殖效率，降低人工成本。</p><h3>2. 科学营养配方</h3><p>根据动物不同生长阶段的营养需求，配制科学的饲料配方，提高饲料转化率。</p><h3>3. 疾病预防</h3><p>建立完善的疫病防控体系，定期进行疫苗接种和健康检查。</p></body></html>",
                null, "养殖顾问", 1, "养殖技术"
            ),
            new com.example.supportingagriculturaltreasures.bean.News(
                "2024年农业政策解读",
                "国家出台了一系列惠农政策，为农业发展提供了强有力的支持。",
                "<html><body><h2>2024年农业政策解读</h2><p>2024年，国家继续加大对农业的支持力度：</p><h3>1. 农业补贴政策</h3><p>继续实施耕地地力保护补贴、农机购置补贴等政策，支持农民购买先进农机设备。</p><h3>2. 农村电商发展</h3><p>大力发展农村电商，拓宽农产品销售渠道，增加农民收入。</p><h3>3. 绿色农业发展</h3><p>推广绿色种植技术，减少化肥农药使用，发展生态农业。</p></body></html>",
                null, "政策专家", 1, "农业资讯"
            ),
            new com.example.supportingagriculturaltreasures.bean.News(
                "常见农作物病虫害防治指南",
                "及时识别和防治农作物病虫害，是保证农作物丰产的重要措施。",
                "<html><body><h2>常见农作物病虫害防治指南</h2><p>农作物病虫害防治是农业生产中的重要环节：</p><h3>1. 预防为主</h3><p>采用抗病品种，合理轮作，清洁田园，减少病虫害发生。</p><h3>2. 生物防治</h3><p>利用天敌昆虫、生物农药等方法进行防治，减少化学农药使用。</p><h3>3. 化学防治</h3><p>在必要时选用低毒、高效的农药，严格按照用药说明使用。</p></body></html>",
                null, "植保专家", 1, "病虫防治"
            )
        };

        for (com.example.supportingagriculturaltreasures.bean.News news : sampleNews) {
            news.setCreateTime(currentTime);
            news.setUpdateTime(currentTime);
            addNews(news);
        }
    }

    private String getCurrentTimeString() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        return sdf.format(new Date());
    }

    // ========== 农友圈相关方法 ==========

    /**
     * 添加农友圈
     */
    public boolean addFarmerCircle(com.example.supportingagriculturaltreasures.bean.FarmerCircle farmerCircle) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        
        values.put(COLUMN_FARMER_CIRCLE_CONTENT, farmerCircle.getContent());
        values.put(COLUMN_FARMER_CIRCLE_IMAGE_URL, farmerCircle.getImageUrl());
        values.put(COLUMN_FARMER_CIRCLE_AUTHOR_NAME, farmerCircle.getAuthorName());
        values.put(COLUMN_FARMER_CIRCLE_AUTHOR_ID, farmerCircle.getAuthorId());
        values.put(COLUMN_FARMER_CIRCLE_LOCATION, farmerCircle.getLocation());
        values.put(COLUMN_FARMER_CIRCLE_LIKE_COUNT, farmerCircle.getLikeCount());
        values.put(COLUMN_FARMER_CIRCLE_COMMENT_COUNT, farmerCircle.getCommentCount());
        values.put(COLUMN_FARMER_CIRCLE_CREATE_TIME, getCurrentTimeString());
        values.put(COLUMN_FARMER_CIRCLE_UPDATE_TIME, getCurrentTimeString());
        
        long result = db.insert(TABLE_FARMER_CIRCLE, null, values);
        db.close();
        
        return result != -1;
    }

    /**
     * 获取所有农友圈
     */
    public List<com.example.supportingagriculturaltreasures.bean.FarmerCircle> getAllFarmerCircles() {
        List<com.example.supportingagriculturaltreasures.bean.FarmerCircle> circleList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        String selectQuery = "SELECT * FROM " + TABLE_FARMER_CIRCLE + " ORDER BY " + COLUMN_FARMER_CIRCLE_CREATE_TIME + " DESC";
        Cursor cursor = db.rawQuery(selectQuery, null);
        
        if (cursor.moveToFirst()) {
            do {
                com.example.supportingagriculturaltreasures.bean.FarmerCircle circle = new com.example.supportingagriculturaltreasures.bean.FarmerCircle();
                circle.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_ID)));
                circle.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_CONTENT)));
                circle.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_IMAGE_URL)));
                circle.setAuthorName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_AUTHOR_NAME)));
                circle.setAuthorId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_AUTHOR_ID)));
                circle.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_LOCATION)));
                circle.setLikeCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_LIKE_COUNT)));
                circle.setCommentCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_COMMENT_COUNT)));
                circle.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_CREATE_TIME)));
                circle.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_UPDATE_TIME)));
                
                circleList.add(circle);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        
        return circleList;
    }

    /**
     * 点赞农友圈
     */
    public boolean likeFarmerCircle(int circleId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_FARMER_CIRCLE + " SET " + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " = " + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " + 1 WHERE " + COLUMN_FARMER_CIRCLE_ID + " = ?", new String[]{String.valueOf(circleId)});
        db.close();
        return true;
    }

    /**
     * 取消点赞农友圈
     */
    public boolean unlikeFarmerCircle(int circleId) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("UPDATE " + TABLE_FARMER_CIRCLE + " SET " + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " = CASE WHEN " + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " > 0 THEN " + COLUMN_FARMER_CIRCLE_LIKE_COUNT + " - 1 ELSE 0 END WHERE " + COLUMN_FARMER_CIRCLE_ID + " = ?", new String[]{String.valueOf(circleId)});
        db.close();
        return true;
    }

    /**
     * 根据ID获取农友圈
     */
    public com.example.supportingagriculturaltreasures.bean.FarmerCircle getFarmerCircleById(int circleId) {
        SQLiteDatabase db = this.getReadableDatabase();
        
        String selectQuery = "SELECT * FROM " + TABLE_FARMER_CIRCLE + " WHERE " + COLUMN_FARMER_CIRCLE_ID + " = ?";
        Cursor cursor = db.rawQuery(selectQuery, new String[]{String.valueOf(circleId)});
        
        com.example.supportingagriculturaltreasures.bean.FarmerCircle circle = null;
        
        if (cursor.moveToFirst()) {
            circle = new com.example.supportingagriculturaltreasures.bean.FarmerCircle();
            circle.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_ID)));
            circle.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_CONTENT)));
            circle.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_IMAGE_URL)));
            circle.setAuthorName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_AUTHOR_NAME)));
            circle.setAuthorId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_AUTHOR_ID)));
            circle.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_LOCATION)));
            circle.setLikeCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_LIKE_COUNT)));
            circle.setCommentCount(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_COMMENT_COUNT)));
            circle.setCreateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_CREATE_TIME)));
            circle.setUpdateTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_FARMER_CIRCLE_UPDATE_TIME)));
        }
        
        cursor.close();
        db.close();
        
        return circle;
    }

    /**
     * 删除农友圈
     */
    public boolean deleteFarmerCircle(int circleId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int rowsAffected = db.delete(TABLE_FARMER_CIRCLE, COLUMN_FARMER_CIRCLE_ID + " = ?", new String[]{String.valueOf(circleId)});
        db.close();
        
        return rowsAffected > 0;
    }

    // ========== 聊天相关方法 ==========

    /**
     * 创建或获取聊天室
     */
    public String createOrGetChatRoom(int userId, int merchantId, String userName, String merchantName) {
        String chatRoomId = userId + "_" + merchantId;
        SQLiteDatabase db = this.getWritableDatabase();
        
        // 检查聊天室是否已存在
        Cursor cursor = db.query(TABLE_CHAT_ROOMS, null, 
                COLUMN_CHAT_ROOM_ID + "=?", new String[]{chatRoomId}, 
                null, null, null);
        
        if (cursor.getCount() == 0) {
            // 不存在则创建新聊天室
            ContentValues values = new ContentValues();
            values.put(COLUMN_CHAT_ROOM_ID, chatRoomId);
            values.put(COLUMN_CHAT_ROOM_USER_ID, userId);
            values.put(COLUMN_CHAT_ROOM_MERCHANT_ID, merchantId);
            values.put(COLUMN_CHAT_ROOM_USER_NAME, userName);
            values.put(COLUMN_CHAT_ROOM_MERCHANT_NAME, merchantName);
            values.put(COLUMN_CHAT_ROOM_LAST_MESSAGE, "");
            values.put(COLUMN_CHAT_ROOM_LAST_MESSAGE_TIME, getCurrentTimeString());
            values.put(COLUMN_CHAT_ROOM_CREATE_TIME, getCurrentTimeString());
            values.put(COLUMN_CHAT_ROOM_UPDATE_TIME, getCurrentTimeString());
            
            db.insert(TABLE_CHAT_ROOMS, null, values);
        }
        
        cursor.close();
        db.close();
        return chatRoomId;
    }

    /**
     * 发送消息
     */
    public boolean sendMessage(String chatRoomId, int senderId, String senderName, String content) {
        SQLiteDatabase db = this.getWritableDatabase();
        String currentTime = getCurrentTimeString();
        String messageId = chatRoomId + "_" + System.currentTimeMillis();
        
        // 添加消息
        ContentValues messageValues = new ContentValues();
        messageValues.put(COLUMN_CHAT_MESSAGE_ID, messageId);
        messageValues.put(COLUMN_CHAT_MESSAGE_ROOM_ID, chatRoomId);
        messageValues.put(COLUMN_CHAT_MESSAGE_SENDER_ID, senderId);
        messageValues.put(COLUMN_CHAT_MESSAGE_SENDER_NAME, senderName);
        messageValues.put(COLUMN_CHAT_MESSAGE_CONTENT, content);
        messageValues.put(COLUMN_CHAT_MESSAGE_TYPE, 0); // 文本消息
        messageValues.put(COLUMN_CHAT_MESSAGE_SEND_TIME, currentTime);
        messageValues.put(COLUMN_CHAT_MESSAGE_IS_READ, 0); // 未读
        
        long messageResult = db.insert(TABLE_CHAT_MESSAGES, null, messageValues);
        
        // 更新聊天室最后消息信息
        ContentValues roomValues = new ContentValues();
        roomValues.put(COLUMN_CHAT_ROOM_LAST_MESSAGE, content);
        roomValues.put(COLUMN_CHAT_ROOM_LAST_MESSAGE_TIME, currentTime);
        roomValues.put(COLUMN_CHAT_ROOM_UPDATE_TIME, currentTime);
        
        int roomResult = db.update(TABLE_CHAT_ROOMS, roomValues, 
                COLUMN_CHAT_ROOM_ID + "=?", new String[]{chatRoomId});
        
        db.close();
        return messageResult != -1 && roomResult > 0;
    }

    /**
     * 获取用户的聊天室列表
     */
    public List<com.example.supportingagriculturaltreasures.bean.ChatRoom> getChatRoomsByUserId(int userId) {
        List<com.example.supportingagriculturaltreasures.bean.ChatRoom> chatRooms = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_CHAT_ROOMS, null, 
                COLUMN_CHAT_ROOM_USER_ID + "=? OR " + COLUMN_CHAT_ROOM_MERCHANT_ID + "=?", 
                new String[]{String.valueOf(userId), String.valueOf(userId)}, 
                null, null, COLUMN_CHAT_ROOM_UPDATE_TIME + " DESC");
        
        if (cursor.moveToFirst()) {
            do {
                com.example.supportingagriculturaltreasures.bean.ChatRoom chatRoom = 
                        new com.example.supportingagriculturaltreasures.bean.ChatRoom();
                chatRoom.setId(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_ID)));
                chatRoom.setUserId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_USER_ID)));
                chatRoom.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_MERCHANT_ID)));
                chatRoom.setUserName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_USER_NAME)));
                chatRoom.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_MERCHANT_NAME)));
                chatRoom.setLastMessage(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_LAST_MESSAGE)));
                chatRoom.setLastMessageTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_ROOM_LAST_MESSAGE_TIME)));
                
                // 计算未读消息数量
                int unreadCount = getUnreadMessageCount(chatRoom.getId(), userId);
                chatRoom.setUnreadCount(unreadCount);
                
                chatRooms.add(chatRoom);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return chatRooms;
    }

    /**
     * 获取聊天消息
     */
    public List<com.example.supportingagriculturaltreasures.bean.ChatMessage> getChatMessages(String chatRoomId, int currentUserId) {
        List<com.example.supportingagriculturaltreasures.bean.ChatMessage> messages = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        Cursor cursor = db.query(TABLE_CHAT_MESSAGES, null, 
                COLUMN_CHAT_MESSAGE_ROOM_ID + "=?", new String[]{chatRoomId}, 
                null, null, COLUMN_CHAT_MESSAGE_SEND_TIME + " ASC");
        
        if (cursor.moveToFirst()) {
            do {
                com.example.supportingagriculturaltreasures.bean.ChatMessage message = 
                        new com.example.supportingagriculturaltreasures.bean.ChatMessage();
                message.setId(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_ID)));
                message.setChatRoomId(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_ROOM_ID)));
                message.setSenderId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_SENDER_ID)));
                message.setSenderName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_SENDER_NAME)));
                message.setContent(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_CONTENT)));
                message.setMessageType(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_TYPE)));
                message.setSendTime(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_SEND_TIME)));
                message.setRead(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_CHAT_MESSAGE_IS_READ)) == 1);
                message.setSentByMe(message.getSenderId() == currentUserId);
                
                messages.add(message);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return messages;
    }

    /**
     * 获取未读消息数量
     */
    public int getUnreadMessageCount(String chatRoomId, int userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        int count = 0;
        
        Cursor cursor = db.query(TABLE_CHAT_MESSAGES, new String[]{"COUNT(*)"}, 
                COLUMN_CHAT_MESSAGE_ROOM_ID + "=? AND " + COLUMN_CHAT_MESSAGE_SENDER_ID + "!=? AND " + COLUMN_CHAT_MESSAGE_IS_READ + "=0", 
                new String[]{chatRoomId, String.valueOf(userId)}, 
                null, null, null);
        
        if (cursor.moveToFirst()) {
            count = cursor.getInt(0);
        }
        
        cursor.close();
        db.close();
        return count;
    }

    /**
     * 标记消息为已读
     */
    public boolean markMessagesAsRead(String chatRoomId, int userId) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        ContentValues values = new ContentValues();
        values.put(COLUMN_CHAT_MESSAGE_IS_READ, 1);
        
        int result = db.update(TABLE_CHAT_MESSAGES, values, 
                COLUMN_CHAT_MESSAGE_ROOM_ID + "=? AND " + COLUMN_CHAT_MESSAGE_SENDER_ID + "!=?", 
                new String[]{chatRoomId, String.valueOf(userId)});
        
        db.close();
        return result >= 0;
    }

    /**
     * 删除聊天室
     */
    public boolean deleteChatRoom(String chatRoomId) {
        SQLiteDatabase db = this.getWritableDatabase();
        
        // 删除聊天消息
        db.delete(TABLE_CHAT_MESSAGES, COLUMN_CHAT_MESSAGE_ROOM_ID + "=?", new String[]{chatRoomId});
        
        // 删除聊天室
        int result = db.delete(TABLE_CHAT_ROOMS, COLUMN_CHAT_ROOM_ID + "=?", new String[]{chatRoomId});
        
        db.close();
        return result > 0;
    }

    /**
     * 搜索商品
     */
    public List<Product> searchProducts(String keyword, String category) {
        List<Product> products = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        String selection = "";
        List<String> selectionArgsList = new ArrayList<>();
        
        if (keyword != null && !keyword.trim().isEmpty()) {
            selection += "(" + COLUMN_PRODUCT_NAME + " LIKE ? OR " + 
                        COLUMN_PRODUCT_CATEGORY + " LIKE ? OR " + 
                        COLUMN_PRODUCT_MERCHANT_NAME + " LIKE ?)";
            String searchKeyword = "%" + keyword.trim() + "%";
            selectionArgsList.add(searchKeyword);
            selectionArgsList.add(searchKeyword);
            selectionArgsList.add(searchKeyword);
        }
        
        if (category != null && !category.trim().isEmpty()) {
            if (!selection.isEmpty()) {
                selection += " AND ";
            }
            selection += COLUMN_PRODUCT_CATEGORY + " LIKE ?";
            selectionArgsList.add("%" + category.trim() + "%");
        }
        
        String[] selectionArgs = selectionArgsList.toArray(new String[0]);
        
        Cursor cursor = db.query(TABLE_PRODUCTS, null, 
                selection.isEmpty() ? null : selection, 
                selectionArgs.length == 0 ? null : selectionArgs, 
                null, null, COLUMN_PRODUCT_CREATE_TIME + " DESC");
        
        if (cursor.moveToFirst()) {
            do {
                Product product = new Product();
                product.setId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_ID)));
                product.setName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_NAME)));
                product.setPrice(cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_PRICE)));
                product.setLocation(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LOCATION)));
                product.setContactPhone(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CONTACT_PHONE)));
                product.setImageUrl(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_IMAGE_URL)));
                product.setCategory(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_CATEGORY)));
                product.setShopName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_SHOP_NAME)));
                product.setMerchantName(cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_NAME)));
                product.setMerchantId(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_MERCHANT_ID)));
                product.setLikes(cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_PRODUCT_LIKES)));
                products.add(product);
            } while (cursor.moveToNext());
        }
        
        cursor.close();
        db.close();
        return products;
    }

    /**
     * 清除测试商品数据（用于重新创建）
     */
    public void clearTestProducts() {
        User testMerchant = loginUser("testmerchant", "123456");
        if (testMerchant == null) return;
        
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_PRODUCTS, COLUMN_PRODUCT_MERCHANT_ID + "=?", 
                new String[]{String.valueOf(testMerchant.getId())});
        db.close();
    }
} 